
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;

// 用户界面类
public partial class C : Page
{
    protected static DbHandler dbHandler = new DbHandler();

    protected void Page_Load(object sender,EventArgs ev)
    {
        DbHandler db = dbHandler;
        switch(Request.Form["Op"])
        {
            case "connect" :     // 执行数据库连接
                if( db.IsConnected ) break;
                try{
                    db.Connect();
                    lblResult.Text = UiHelper.Msg("数据库连接成功");
                }catch(Exception ex){
                    lblResult.Text = UiHelper.Msg(ex.Message);
                }
                break;
            case "deconnect" :   // 断开数据库连接
                if( !db.IsConnected ) break;
                try{
                    db.Deconnect(); 
                    lblResult.Text = UiHelper.Msg("数据库断开成功");
                }catch( Exception ex ){
                    lblResult.Text = UiHelper.Msg(ex.Message);
                }
                break;
            case "select" :       // 执行查询
                try{
                    Session["formatString"] = Request.Form["formatString"];
                    Session["checkedSelect"] = true;
                    Session["checkedExecute"] = false;
                    DataTable tb = db.Select();
                    if( null==Session["formatString"] || string.IsNullOrEmpty(Session["formatString"].ToString()) )
                        lblResult.Text = UiHelper.Output(tb);
                    else
                        lblResult.Text = UiHelper.Output(tb,Session["formatString"].ToString());
                }catch( Exception ex ){
                    lblResult.Text = UiHelper.Msg(ex.Message);
                }
                break;
            case "execute" :
                Session["checkedSelect"] = false;
                Session["checkedExecute"] = true;
                try{
                    lblResult.Text = UiHelper.Msg(string.Format("受影响的行数：{0}行", db.Execute()) );
                }catch( Exception ex ){
                    lblResult.Text = UiHelper.Msg(ex.Message);
                }
                break;
        }
    }
}

// 输入控制器类
public class DbHandler
{
    private DbClient _db;
    
    public void Connect()
    {
        HttpContext.Current.Session["ConnectionString"] = HttpContext.Current.Request.Form["ConnectionString"];
        
        if( null==HttpContext.Current.Session["ConnectionString"] || string.IsNullOrEmpty(HttpContext.Current.Session["ConnectionString"].ToString()) )
            throw new ArgumentNullException("连接字符串不能为空!");

        if (null == _db)
            _db = new DbClient(HttpContext.Current.Session["ConnectionString"].ToString());
        else
            _db.Connect(HttpContext.Current.Session["ConnectionString"].ToString());
    }

    public void Deconnect()
    {
        HttpContext.Current.Session["ConnectionString"] = "";

        if( null!=_db )
            _db.Deconnect();
    }

    public bool IsConnected
    {
        get
        {
            if( null==_db )
                return false;
            return _db.IsConnected;
        }
    }

    public DataTable Select()
    {
        HttpContext.Current.Session["sql"] = HttpContext.Current.Request.Form["sql"];
        
        if( null==HttpContext.Current.Session["sql"] || string.IsNullOrEmpty(HttpContext.Current.Session["sql"].ToString()) )
            throw new Exception( "请输入sql命令" );
            
        string sql = HttpContext.Current.Session["sql"].ToString();

        return _db.Select(sql);
    }
    
    public int Execute()
    {
        HttpContext.Current.Session["sql"] = HttpContext.Current.Request.Form["sql"];
        
        if( null==HttpContext.Current.Session["sql"] || string.IsNullOrEmpty(HttpContext.Current.Session["sql"].ToString()) )
            throw new Exception( "请输入sql命令" );
            
        string sql = HttpContext.Current.Session["sql"].ToString();
        
        return _db.Execute(sql);
    }
    
}

// 界面帮助类
public class UiHelper
{
    // 消息
    public static string Msg( string s )
    {
        return s;
    }
    
    // 输出表格数据
    public static string Output(DataTable table)
    {
        StringBuilder sb = new StringBuilder("<table>");
        
        // 列名
        sb.AppendLine("<tr>");
        foreach( DataColumn col in table.Columns ){
            sb.AppendLine( string.Format("<th>{0}</th>",col.ColumnName) );
        }
        sb.AppendLine("</tr>");
        
        // 数据
        foreach( DataRow row in table.Rows ){
            sb.AppendLine("<tr>");
            foreach( DataColumn col in table.Columns ){
                sb.AppendLine( string.Format("<td>{0}</td>",row[col]) );
            }
            sb.AppendLine("</tr>");
        }

        sb.AppendLine("</table>");
        return sb.ToString();
    }
    
    // 输出指定格式数据
    public static string Output(DataTable table,string formatStr)
    {
        StringBuilder sb = new StringBuilder("<pre>");

        // 数据
        int len = table.Columns.Count;
        foreach( DataRow row in table.Rows ){
            
            string[] ss = new string[len];
            for( int i=0;i<len;i++ ){
                ss[i] = ToSqlParam(row[i]);
            }
            sb.AppendLine( string.Format(formatStr,ss) );
        }
        sb.AppendLine("</pre>");
        return sb.ToString();
    }
    
    public static string ToSqlParam(object obj)
    {
        if( typeof(DBNull)==obj.GetType() )
            return "null";
        if( typeof(Boolean)==obj.GetType() )
            return true==(bool)obj ? "1" : "0";
        if( typeof(Byte)==obj.GetType() )
            return obj.ToString();
        if( typeof(Char)==obj.GetType() )
            return string.Format("'{0}'",obj.ToString());
        if( typeof(DateTime)==obj.GetType() )
            return string.Format("'{0}'",obj.ToString());
        if( typeof(Decimal)==obj.GetType() )
            return obj.ToString();
        if( typeof(Double)==obj.GetType() )
            return obj.ToString();
        if( typeof(Int16)==obj.GetType() )
            return obj.ToString();
        if( typeof(Int32)==obj.GetType() )
            return obj.ToString();
        if( typeof(Int64)==obj.GetType() )
            return obj.ToString();
        if( typeof(SByte)==obj.GetType() )
            return obj.ToString();
        if( typeof(Single)==obj.GetType() )
            return obj.ToString();
        if( typeof(String)==obj.GetType() )
            return string.Format("'{0}'",obj.ToString());
        if( typeof(TimeSpan)==obj.GetType() )
            return string.Format("'{0}'",obj.ToString());
        if( typeof(UInt16)==obj.GetType() )
            return obj.ToString();
        if( typeof(UInt32)==obj.GetType() )
            return obj.ToString();
        if( typeof(UInt64)==obj.GetType() )
            return obj.ToString();
        
        return "null";
    }
}

// 核心类
public class DbClient
{
    private SqlConnection _conn = null;
    
    // 构造函数
    public DbClient(){}
    public DbClient(string connStr)
    {
        Connect(connStr);
    }
    
    // 建立连接对象
    public void Connect( string connStr )
    {
        _conn = new SqlConnection(connStr);
    }
    // 销毁连接对象
    public void Deconnect()
    {
        if( null==_conn )
            return;

        CloseConn(_conn);
        _conn.Dispose();
        _conn = null;
    }
    // 是否已建立连接对象
    public bool IsConnected
    {
        get{ return null!=_conn;  }
    }
    
    // 执行非查询语句
    public int Execute( string sql )
    {
        sql = PreProcessSql(sql);
        
        SqlCommand command = new SqlCommand(sql);
        command.CommandTimeout = 180;
        
        OpenConn(_conn);
        command.Connection = _conn;
        int i = command.ExecuteNonQuery();
        CloseConn(_conn);
        return i;
    }
    
    // 执行查询语句
    public DataTable Select( string sql )
    {
        sql = PreProcessSql(sql);
        
        OpenConn(_conn);
        SqlDataAdapter datAdapter = new SqlDataAdapter(sql, _conn);

        DataTable table = new DataTable();
        datAdapter.Fill(table);

        CloseConn(_conn);
        return table;
    }
    
    // 预处理sql
    private string PreProcessSql( string sql )
    {
        if( string.IsNullOrEmpty(sql) )
            return string.Empty;

        sql = sql.Trim().ToLower();             // 清除两端空白字符
        sql = Regex.Replace(sql,"--.*","");     // 清除注释
        return sql;
    }
    // 打开连接
    private void OpenConn(SqlConnection conn)
    {
        if (ConnectionState.Open != conn.State)
        {
            _conn.Open();
        }
    }
    // 关闭连接
    private void CloseConn(SqlConnection conn)
    {

        if (ConnectionState.Open == conn.State)
        {
            _conn.Close();
        }
    }
}

